#查询操作fetchall(),fetchmany(),fetchone()
# import pymysql
# db=pymysql.connect(
#     host="localhost",
#     port=3307,
#     database="zzh",
#     user='root',
#     password='1234',
#     charset='utf8'#主机，端口号，数据库，用户名，密码，编码,只有端口号是int型
# )
# cursor=db.cursor()
# sql="select * from scores"
# cursor.execute(sql)
# data=cursor.fetchall()
# columns=['score_id','student_id','course_id',"score",'exam_date']
# print(f"{'|'.join(columns)}")
# for row in data:
#     print(f"{row}")
# cursor.close()
# db.close()
# cursor=db.cursor()
# sql="select * from scores"
# cursor.execute(sql)
# data=cursor.fetchone()
# print(f"获取的数据如下：{data}")
# data=cursor.fetchone()
# print(f"获取的数据如下：{data}")
# data=cursor.fetchone()
# print(f"获取的数据如下：{data}")
# cursor.close()
# db.close()
#插入操作 插入单条数据，插入多条数据
# import pymysql
# db=pymysql.connect(
#     host="localhost",
#     port=3307,
#     database="zzh",
#     user='root',
#     password='1234',
#     charset='utf8'#主机，端口号，数据库，用户名，密码，编码,只有端口号是int型
# )
# try:
#     with db.cursor() as cursor:
#         value=("(13,'周五','Male',22,'数据库'),"
#                "(14,'周五','Male',22,'数据库'),"
#                "(15,'周五','Male',22,'数据库')")
#         sql=f"insert into students values {value}"
#         cursor.execute(sql)
#         db.commit()
# except Exception as e:
#     print("数据库操作异常：\n",e)
# finally:
#     db.close()
#异常捕获下的删除，更新，查询操作
# import pymysql
# #创建connect
# db = pymysql.connect(
#     host = 'localhost',
#     port = 3307,
#     database = 'zzh',
#     user = 'root',
#     password = '1234',
#     charset = 'utf8')
# try:
#     with db.cursor() as cursor:
#         # sql="update students set major='%s',age=%s where student_id=%s"%("英语",18,15)
#         # sql="delete from students where student_id=%s"%(15)
#         sql='select * from students '
#         cursor.execute(sql)
#         result=cursor.fetchmany(3)
#         print("获取的数据：\n",result)
#         db.commit()
# except Exception as e:
#     print("数据库操作异常：\n",e)
# finally:
#     db.close()
#封装工具
import pymysql
class DButils:
    def __init__(self,host,port,user,password,database,charset):
        self.host=host
        self.port=port
        self.user=user
        self.password=password
        self.database=database
        self.charset=charset
    global db
    def update(self,sql,param=None):
        db=pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset)
        try:
            with db.cursor() as cursor:
                cursor.execute(sql,param)
                db.commit()
                print("数据操作成功：")
        except Exception as e:
            print("数据操作失败!",e)
        finally:
            db.close()
    def select(self,sql,param=None):
        db = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset)
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, param)
                result=cursor.fetchall()
                print("数据查询成功")
                return result
        except Exception as e:
            print("数据查询失败!", e)
        finally:
            db.close()
    def input_person_info(self,count):
        '''输入单个的个人信息'''
        print(f"\n===输入第{count}个人的信息===")
        username=input("姓名").strip()
        while not username:
            print("姓名不能为空！")
            username = input("姓名").strip()
        while True:
            age=input("年龄：")
            if age.isdigit() and 0 < int(age) <120:
                age=int(age)
                break
            print("请输入有效的年龄")
        while True:
            sex=input("请输入性别：")
            if sex == "男" or '女':
                sex=sex
                break
            print('请输入有效性别')
        high=float(input("请输入身高"))
        return (username,age,sex,high)
    def save_person(self,person_data):
        db = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            database=self.database,
            charset=self.charset)
        try:
            with db.cursor() as cursor:
                sql="""insert into person (username,age,sex,high) 
                values (%s,%s,%s,%s)
                    """
                cursor.execute(sql,person_data)
                db.commit()
                print("数据保存成功")
        except Exception as e:
            print("保存数据失败",e)
        finally:
            db.close()
if __name__ == '__main__':
    db=DButils('127.0.0.1',3307,'root','1234','zzh','utf8')
    # sql="select * from students where student_id = %s"
    # data = 1
    # result=db.select(sql,data)
    # print(result)
    #%s表示一个占位符
    # sql="insert into students values (%s,%s,%s,%s,%s)"
    # param=(15,'张三', 'Male', 21, '计算机科学')
    # db.update(sql,param)
    # sql="delete from students where student_id = %s"
    # data = 13
    # db.update(sql,data)
    # sql="update students set major = '英语' where student_id = %s"
    # data = 12
    # db.update(sql,data)
    #编程实现：循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
    for i in range(1,11):
        person_data = db.input_person_info(i)
        db.save_person(person_data)
    print("\n所有信息已录入完成")











































































































































































































































































































































































































































































































































































































































